-- Change the case-mediafile relationship from one-many to many-many.

BEGIN;

CREATE TABLE "docket_mediafile_cases" (
    "id" serial NOT NULL PRIMARY KEY,
    "mediafile_id" integer NOT NULL REFERENCES "docket_mediafile" ("id") DEFERRABLE INITIALLY DEFERRED,
    "case_id" integer NOT NULL REFERENCES "docket_case" ("id") DEFERRABLE INITIALLY DEFERRED,
    UNIQUE ("mediafile_id", "case_id")
);

INSERT INTO docket_mediafile_cases (mediafile_id, case_id)
SELECT id, case_id FROM docket_mediafile;

ALTER TABLE docket_mediafile DROP COLUMN case_id;

COMMIT;
